fig = px.line(reqs_daily, x="DATE_RAISED", y=['request count', '28 day rolling mean'], title="Request counts over time")
fig.show()

fig = px.line(reqs_daily, x="day of year", y=['7 day rolling mean'], color='year', title='Request counts - year over year')
fig.show()

print('2013 to 2015 CHANNELS:')
reqs[reqs.year.isin((2013,2014,2015))].CHANNEL.value_counts().reset_index()

print('\n\n\n2016 to 2021 CHANNELS:')
reqs[reqs.year >= 2016].CHANNEL.value_counts().reset_index()

print("Top 5 days by request count")
reqs_daily.sort_values(by='request count', ascending=False).iloc[:5]

print('TOP 10 SERVICE REQUEST TYPES ON FEB 5th, 2019:\n')
print(reqs[reqs.DATE_RAISED == date(2019, 2, 5)].groupby('TYPE').SUBJECT.count().sort_values(ascending=False).iloc[:10])
print('\n\nTOP 10 SERVICE REQUEST TYPES ON FEB 25th, 2019:\n')
print(reqs[reqs.DATE_RAISED == date(2019, 2, 25)].groupby('TYPE').SUBJECT.count().sort_values(ascending=False).iloc[:10])

reqs_by_ward = (
    reqs[reqs.year >= 2016]
    .groupby(["DATE_RAISED", "WARD"], as_index=False)
    .TYPE.count()
    .rename(columns={'TYPE': 'request count'})
    .sort_values(by=['WARD', 'DATE_RAISED'])
)
# Remove no-name ward
reqs_by_ward = reqs_by_ward[reqs_by_ward.WARD != ' ']

#Tried to do this with a groupby.rolling but wasn't working for some reason...
ward_list = []
for ward in reqs_by_ward.WARD.unique().tolist():
    df_ward = reqs_by_ward[reqs_by_ward.WARD == ward]
    df_ward.loc[:,'28 day rolling mean'] = df_ward.loc[:,'request count'].rolling(window=28).mean()
    ward_list.append(df_ward)
reqs_by_ward = pd.concat(ward_list)


fig = px.line(reqs_by_ward, x="DATE_RAISED", y='28 day rolling mean', color='WARD', height=500, width=800,
             title='Requests by Ward over time')
fig.update_traces(line={'width': 1.1})
fig.show()

fig = px.line(reqs_by_ward[reqs_by_ward.DATE_RAISED >= date(2020,1,1)], x="DATE_RAISED", y='28 day rolling mean', color='WARD', height=500, width=800,
             title='Requests by Ward over time (2020/21)')
fig.show()

# Create figure with secondary y-axis
fig = make_subplots(specs=[[{"secondary_y": True}]])

# Add traces
fig.add_trace(
    go.Bar(x=reqs_per_ward.WARD, y=reqs_per_ward['avg annual requests'], name="requests per year"),
    secondary_y=False,
)

fig.add_trace(
    go.Scatter(x=reqs_per_ward.WARD, y=reqs_per_ward['avg annual requests per capita'], name="requests per capita"),
    secondary_y=True,
)

# Add figure title
fig.update_layout(
    title_text="Requests per year & per capita",
    height=600
)

# Set x-axis title
fig.update_xaxes(
    title_text="Ward",
    tickangle=45
)

# Set y-axes titles
fig.update_yaxes(title_text="Total requests", secondary_y=False)
fig.update_yaxes(title_text="Requests per capita", secondary_y=True)

fig.show()

for ward in reqs.WARD.unique().tolist():
    if ward == ' ' or str(ward) == 'nan':
        continue
    reqs_ward = reqs[reqs.WARD == ward]
    reqs_ward = reqs_ward.groupby(['TYPE'], as_index=False).CHANNEL.count().rename(columns={'CHANNEL':'request count'})
    reqs_ward.sort_values(by=['request count'], ascending=False, inplace=True)
    # Top 10 reqests
    reqs_ward = reqs_ward.iloc[:10,:]
    plt.figure(figsize=(10,4))
    plt.bar(reqs_ward['TYPE'], reqs_ward['request count'])
    plt.xticks(rotation=-30, ha='left')
    plt.title(f'{ward} - top 10 request types')
    plt.show()

distances

top_ten_reqs = reqs.groupby('TYPE').TYPE.count().sort_values(ascending=False).iloc[:12]
reqs_in_top10 = reqs[reqs.TYPE.isin(top_ten_reqs.index.tolist())]
reqs_by_day_top10 = reqs_in_top10.groupby(['DATE_RAISED', 'TYPE'], as_index=False).CHANNEL.count().rename(columns={'CHANNEL': 'request count'}).sort_values(by=['TYPE','DATE_RAISED']).reset_index(drop=True)
reqs_by_day_top10['rolling avg'] = reqs_by_day_top10.groupby('TYPE')['request count'].rolling(7).mean().reset_index(drop=True).tolist()
reqs_by_day_top10 = reqs_by_day_top10[reqs_by_day_top10.DATE_RAISED >= date(2020,1,1)]
fig = px.line(reqs_by_day_top10, x="DATE_RAISED", y='rolling avg', color='TYPE')
fig.show()

reqs_dog = reqs[reqs.TYPE.str.lower().str.contains('dog|pit bull')]
reqs_dog_daily = reqs_dog.groupby(['DATE_RAISED','day of year','year'], as_index=False).TYPE.count()
fig = px.line(reqs_dog_daily, x="DATE_RAISED", y='TYPE')
fig.show()
fig = px.line(reqs_dog_daily, x="day of year", y='TYPE', color='year')
fig.show()

reqs_dog_daily_type = reqs_dog.groupby(['DATE_RAISED','day of year','year', 'TYPE'], as_index=False).SUBJECT.count()
fig = px.line(reqs_dog_daily_type, x="DATE_RAISED", y='SUBJECT', color='TYPE')
fig.show()

fig = px.line(reqs.groupby(['month raised', 'SUBJECT'],as_index=False).TYPE.count(), x='month raised', y='TYPE', color='SUBJECT')
fig.show()

# WAP = Wireless application protocol (aka mobile app)
reqs.loc[:,'CHANNEL'] = reqs['CHANNEL'].str.replace('WAP', 'Mobile app')
reqs_by_day_channel = reqs.groupby(['month raised', 'CHANNEL'], as_index=False).agg({'SUBJECT': 'count'}).rename(columns={'SUBJECT': 'request count'})
fig = px.line(reqs_by_day_channel, x="month raised", y='request count', color='CHANNEL', title='Requests by channel')
fig.show()

# Outlier detection
reqs_by_day_type = reqs.groupby(['DATE_RAISED', 'TYPE'], as_index=False).SUBJECT.count().rename(columns={'SUBJECT': 'request count'})
reqs_by_type_stats = reqs_by_day_type.groupby('TYPE', as_index=False).agg({'request count': ['sum', 'mean', 'std', 'median', lambda x: np.quantile(x, .95), lambda x: np.quantile(x, .99), lambda x: np.abs(x-x.median()).median(), lambda x: np.abs(x-x.mean()).median()]})
reqs_by_type_stats.columns = ['TYPE', 'sum', 'avg', 'stddev', 'med', 'pct_95', 'pct_99', 'MAD','MeanAD']
reqs_by_day_type_stats = reqs_by_day_type.merge(reqs_by_type_stats, on='TYPE')
reqs_by_day_type_stats['z_score'] = (reqs_by_day_type_stats['request count']-reqs_by_day_type_stats.avg )/(reqs_by_day_type_stats.stddev + .001)
reqs_by_day_type_stats['modified_z_score'] = reqs_by_day_type_stats.apply(lambda x: modified_z_score(x), axis=1)
reqs_by_day_type_stats.sort_values(by='modified_z_score', ascending=False)

reqs_by_type = reqs.groupby('TYPE',as_index=False).SUBJECT.count().rename(columns={'SUBJECT': 'request count'})
reqs_by_type.sort_values(by='request count', ascending=False, inplace=True)
reqs_by_type['pct of total requests'] = reqs_by_type['request count'] / reqs_by_type['request count'].sum()
reqs_by_type['cumulative pct of total requests'] = reqs_by_type['pct of total requests'].cumsum()
reqs_by_type

reqs